set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dwm.dwm_analysis_target_network_sum_month_dt
select
     substr(trunc(stat_date,'MM'),1,7) as stat_date
    ,network_type
    ,network_code
    ,network_name
    ,franchisee_code
    ,franchisee_name
    ,agent_code
    ,agent_name
    ,virt_code
    ,virt_name
    ,kpi_code
    ,kpi_name
    ,kpi_type
    ,sum(kpi_fz) as kpi_fz
    ,sum(kpi_fm) as kpi_fm
    ,sum(kpi_fz) / sum(kpi_fm) as kpi_values
    ,kpi_param
    ,agent_principal_name
    ,franchisee_principal_name
    ,principal_name
    ,kpi_alias
    ,substr(trunc(dt,'MM'),1,7) as dt
from jms_dwm.dwm_analysis_target_network_sum_day_dt
where dt between add_months(trunc('{{ execution_date | cst_ds }}','MM'),-2) and '{{ execution_date | cst_ds }}'
group by substr(trunc(stat_date,'MM'),1,7)
        ,network_type
        ,network_code
        ,network_name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
        ,virt_code
        ,virt_name
        ,kpi_code
        ,kpi_name
        ,kpi_type
        ,kpi_param
        ,agent_principal_name
        ,franchisee_principal_name
        ,principal_name
        ,kpi_alias
        ,substr(trunc(dt,'MM'),1,7)
distribute by dt,pmod(hash(rand()),1)
;